import pandas as pd #导入pandas

# df = pd.read_excel('../datafile/销售表.xlsx')
# pd.set_option('display.unicode.east_asian_width', True)

# print(df.head())
# df = df.sort_values(by='成交金额',ascending=False)
# print(df.head())

# df = df.sort_values(by=['数量','成交金额'],ascending=False)
# print(df)

# df = df.sort_values(by='数量',ascending=False)
# # print(df)
# df['顺序排名'] = df['数量'].rank(method='min',ascending=False)
# print(df)

# data = [[100,90,80],[98,67,56],[56,56,45]]
# columns = ['数学','语文','英语']
# df = pd.DataFrame(data=data,columns=columns,index=[1,2,3])
# print(df)
# df['总成绩'] = df.sum(axis=1)
# print(df)

# df = df._append(df.mean(),ignore_index=True)
# df = df._append(df.max(),ignore_index=True)
# df = df._append(df.min(),ignore_index=True)


# df = df._append(df.median(),ignore_index=True)


# data = [[100,90,100],[100,76,76],[76,90,76]]
# columns = ['数学','语文','英语']
# df = pd.DataFrame(data=data,columns=columns,index=[1,2,3])
#
# df = df._append(df.median(),ignore_index=True)

# print(df)
# print(df.mode())
# print(df.mode(axis=1))
# print(df['数学'].mode())

# data = [[135,140,138,145,140],[140,145,139,142,132]]
# index = ['李凡','郭曙光']
# columns = ['一模','二模','三模','四模','五模']
# df = pd.DataFrame(data=data,index=index,columns=columns)
# pd.set_option('display.unicode.east_asian_width', True)
# print(df)
# # print(df.var(axis=1))
# print(df.std(axis=1))

# data = [120.110,112,100,98,34,123,115]
# columns = ['数学']
# df = pd.DataFrame(data=data,columns=columns)
# print(df)
# x = df['数学'].quantile(0.35)
# print(x)
# print(df[df['数学']<x])

# df = pd.DataFrame({
#     'A':[1,2],
#     'B':[pd.Timestamp(2020),pd.Timestamp(2021)],
#     'C':[pd.Timedelta('1 days'),pd.Timedelta('2 days')]
# })
#
# print(df.quantile(0.5,numeric_only=False))

#设置小数位置
# df = pd.read_excel('../datafile/数据格式化.xlsx')
# pd.set_option('display.unicode.east_asian_width', True)

# print(df.round(2))
#对指定的列保留小数位置
# print(df.round({'A1':1,'A2':2}))#round中的参数是一个字典

# s1 = pd.Series([1,0,2,1,2],index=['A1','A2','A3','A4','A5'])
#
# df2 = df.round(s1)
# print(df2)

# df3 = df.applymap(lambda x: '{:.2f}'.format(x))
# print(df3)

# df['百分比'] =df['A1'].apply(lambda x:format(x,'.0%'))
# print(df)
# df['百分比'] =df['A1'].apply(lambda x:format(x,'.2%'))
# print(df)
# df['百分比'] =df['A1'].map(lambda x:format(x,'.2%'))
# print(df)


# df = pd.read_excel('../datafile/课程.xlsx')
# pd.set_option('display.unicode.east_asian_width', True)
# df['买家实际支付金额'] = df['买家实际支付金额'].apply(lambda x: format(int(x),','))
# print(df)

#apply(),map(),applymap()区别
#apply()可以在Series,对Series的每一个元素都执行一次函数，也可以在DataFrame中起作用，对某DataFrame中的一行或者
# s = pd.Series(data=[10,20,30,40],index=['a','b','c','d'])
# # print(s)
# s = s.apply(lambda x:x+10)
# print(s)

# df = pd.DataFrame(data=[[10,20,30,40],[11,22,33,44]],index=['a','b'],columns=['A','B','C','D'])
# print(df)
# print('---------------------------------')
# df2 = df.apply(lambda x:x.sum(), axis=0)
# print(df2)
# print('---------------------------------')
# df2 = df.apply(lambda x:x.sum(), axis=1)
# print(df2)

#map只能应用在Series的每个元素上
# df = pd.DataFrame(data=[['男'],['女'],['男'],['男']],index=['张三','李四','王五','陈六'],columns=['性别'])
# # print(df)
#
# def gender(g):
#     if g == '男':
#         return 0
#     else:
#         return 1
# df2 = df['性别'].map(gender)
# # print(df2)
#
# df3 = df['性别'].map({'男': 0, '女': 1})
# print(df3)

#applymap()讲函数应用到DataFrame中的每一个元素中，与apply的区别，apply只用用到到某列或者某行
# df = pd.DataFrame(data=[[10,20,30,40],[11,22,33,44]],index=['a','b'],columns=['A','B','C','D'])
# df2 = df.applymap(lambda x:x+10)
# print(df2)

# df = pd.read_excel('../datafile/销售表.xlsx')
# df1 = df[['产品名称','数量','标准单价']]
# print(df1)
# print('---------------------------------------')
# print(df1.groupby('产品名称',as_index=False).sum())

# df1 = df[['产品名称','销售员','数量']]
# pd.set_option('display.unicode.east_asian_width', True)
# print(df1.groupby(['产品名称','销售员']).sum())
# df1 = df[['产品名称','数量','标准单价']]
# print(df1.groupby('产品名称')['数量'].sum())


# df1 = df[['产品名称','销售员','数量']]
# print(df1.groupby('产品名称'))
# for name,group in df1.groupby('产品名称'):
#     print(name,'----',group)
# df1 = df1.groupby(['产品名称','销售员'])
# print(df1)
# for (k1,k2),group in df1.groupby(['产品名称','销售员']):
    # print(k1,k2)
    # print(group)
    # print('--------------------')

# df = pd.read_excel('../datafile/销售表.xlsx')
# pd.set_option('display.unicode.east_asian_width', True)
# print(df.head())

# df1 = df[['产品名称','数量']]
# print(df1.groupby('产品名称').agg(['sum','mean']))

# df1 = df[['产品名称','数量','成交金额']]
#
# print(df1.groupby('产品名称').agg({'数量':['sum','mean'],'成交金额':['max','min']}))

# print(type(df['产品名称']))
# print(df['产品名称'].value_counts())
#
# maxcount = lambda x:x.value_counts().index[1]
# maxcount.__name__='销量最多的产品'
#
# df1 =df.agg({'产品名称':[maxcount],'数量':['sum']})
# print(df1)

df = pd.read_excel('../datafile/JD手机销售.xlsx')
pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.max_columns',500)
pd.set_option('display.width',1000)
# print(df)
df = df.set_index('商品名称')
dict1 = {'北京出库销量':'华北地区',
         '上海出库销量':'华东地区',
         '广州出库销量':'华南地区',
         '天津出库销量':'华北地区',
         '苏州出库销量':'华东地区',
         '沈阳出库销量':'东北地区',
         '杭州出库销量':'华东地区'
}

# df1 = df.groupby(dict1,axis=1).sum()
# print(df1)

s = pd.Series(dict1)
df1 = df.groupby(s,axis=1).sum()
# print(s)
print(df1)